﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO
Public Class ERP

#Region " CLASS LOGIN "
    Public Class LOGIN

        Public Function CheckLogin(ByVal code As String, ByVal password As String) As ArrayList
            Dim arr As New ArrayList(2)
            Dim fullname As String
            Dim userID As Integer
            Dim values As String
            Dim Level As Integer
            Try
                'Dim str As String = "data source=10.0.0.3\sqlserver;initial catalog=factory;persist security info=False;user id=sa;pwd=ttfi_1985"
                Dim cn As New ERP.CONNECTION 'SqlConnection(str)
                'cn.Open()
                Dim cmd As New SqlCommand("CheckLogin", cn.OpenConnectionSQL("factory"))
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(New SqlParameter("@Code", SqlDbType.VarChar, 5))
                cmd.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 50))
                cmd.Parameters.Add(New SqlParameter("@Fullname", SqlDbType.NVarChar, 50))
                cmd.Parameters.Add(New SqlParameter("@UserID", SqlDbType.Int))
                cmd.Parameters.Add(New SqlParameter("@LevelID", SqlDbType.Int))
                cmd.Parameters("@Code").Value = code
                cmd.Parameters("@Password").Value = password
                cmd.Parameters("@Fullname").Direction = ParameterDirection.Output
                cmd.Parameters("@UserID").Direction = ParameterDirection.Output
                cmd.Parameters("@LevelID").Direction = ParameterDirection.Output
                'con03.Open()
                cmd.ExecuteNonQuery()
                values = cmd.Parameters("@Fullname").Value.ToString
                fullname = cmd.Parameters("@Fullname").Value.ToString
                If values = "" Or IsDBNull(values) Then
                    values = "Invalid username or password !"
                    arr.Add(values)
                Else
                    userID = System.Int32.Parse(cmd.Parameters("@UserID").Value)
                    Level = System.Int32.Parse(cmd.Parameters("@LevelID").Value)
                    arr.Add(values)
                    arr.Add(userID)
                    arr.Add(Level)
                    arr.Add(code)
                    arr.Add(cmd.Parameters("@Password").Value)
                End If
                Return arr
                cn.OpenConnectionSQL("factory").Close()
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            Return arr
        End Function

    End Class
#End Region

#Region " CLASS DRREVIEW "
    Public Class DRREVIEW

        Public Sub styleControl(ByVal chon As Boolean, ByVal sender As Object)
            If chon Then
                sender.BackColor = Color.Yellow
            Else
                sender.BackColor = Color.Lavender
            End If
        End Sub

        Public Function setValueWithImages(ByVal pic As System.Windows.Forms.PictureBox) As Object
            Dim data As Byte() = Nothing
            Try
                Dim ms As New MemoryStream()
                pic.Image.Save(ms, pic.Image.RawFormat)
                data = ms.GetBuffer()
            Catch ex As Exception

            End Try

            Return data
        End Function
        Public Sub customFormatDateTimePicker(ByVal dtp As Object, ByVal strFormat As String)
            If dtp.GetType.ToString = "System.Windows.Forms.DateTimePicker" Then
                dtp.Format = DateTimePickerFormat.Custom
                dtp.CustomFormat = strFormat
            End If
        End Sub
        Public Function maxDateRevised(ByVal ex As String) As Date
            Dim cn As SqlConnection
            cn = New SqlConnection(_str)
            cn.Open()
            Dim cmd As New SqlCommand("SELECT max(CONVERT(DATE, WO_AQL_LIST.RevisedDate)) AS RevisedDate FROM WO_AQL_LIST WHERE CD='" & ex.Trim & "'", cn)
            cmd.CommandType = CommandType.Text
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            While dr.Read
                If IsDBNull(dr(0)) Then
                    Return "#12:00:00 AM#"
                End If
                Return dr(0)
            End While
        End Function

        Public Function minDateRevised(ByVal ex As String) As Date
            Dim cn As SqlConnection
            cn = New SqlConnection(_str)
            cn.Open()
            Dim cmd As New SqlCommand("SELECT min(CONVERT(DATE, WO_AQL_LIST.RevisedDate)) AS RevisedDate FROM WO_AQL_LIST WHERE CD='" & ex.Trim & "'", cn)
            cmd.CommandType = CommandType.Text
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            While dr.Read
                If IsDBNull(dr(0)) Then
                    Return "#12:00:00 AM#"
                End If
                Return dr(0)
            End While
        End Function

        Public Function countDateRevised(ByVal ex As String) As Integer
            Dim cn As SqlConnection
            cn = New SqlConnection(_str)
            cn.Open()
            Dim cmd As New SqlCommand("SELECT COUNT(AQL_ID) as coutID from WO_AQL_LIST t2 where ltrim(rtrim(t2.CD))='" & ex.Trim & "'", cn)
            cmd.CommandType = CommandType.Text
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader
            While dr.Read
                Return dr(0)
            End While
        End Function

        Public Function isFileInUse(ByVal sFileName As String) As Integer
            ' If the file is already opened by another process and the specified type of access
            ' is not allowed the Open operation fails and an error occurs.
            Dim nFileNum As Integer
            nFileNum = FreeFile()
            Try
                FileOpen(nFileNum, sFileName, OpenMode.Binary, OpenAccess.Write)
                FileClose(nFileNum)
                Return False
            Catch
                Return True
            End Try
        End Function

        Public Function IsFileOpen(ByVal filePath As String) As Boolean
            Dim blnResult As Boolean = False
            Try
                Dim fs As System.IO.FileStream = System.IO.File.OpenWrite(filePath)
                fs.Close()
            Catch ex As Exception
                blnResult = True
            End Try
            Return blnResult
        End Function

        Public Sub releaseObject(ByRef obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub

        Public Function SP_Capnhat(ByVal cn As SqlConnection, ByVal sp_name As String, Optional ByVal params() As String = Nothing, Optional ByVal values() As Object = Nothing) As Boolean
            Try
                Dim command As SqlCommand = New SqlCommand(sp_name, cn)
                command.CommandType = CommandType.StoredProcedure
                command.CommandTimeout = 10000
                If (Not params Is Nothing) Then
                    Dim len As Integer = params.Length - 1
                    Dim i As Integer
                    For i = 0 To len
                        command.Parameters.Add(New SqlParameter(params(i), values(i)))
                    Next
                End If
                command.ExecuteNonQuery()
            Catch ex As Exception
                Return False
                'MsgBox(ex.ToString)
                'MsgBox("Đứt kết nối,xin kiểm tra đường truyền và khởi động lại chương trình") 'ex.ToString)
            End Try
            Return True
        End Function

        Public Function getDataFromAccess(ByVal strSQL As String, ByVal _fileAccess As String) As System.Data.DataTable
            Dim tb As New System.Data.DataTable
            'open connection access
            OpenConnectionAccess(_fileAccess)
            Dim cmd As New OleDb.OleDbCommand(strSQL, conAcc)
            Dim da As New OleDb.OleDbDataAdapter
            da.SelectCommand = cmd
            da.Fill(tb)
            Return tb
        End Function

        Public Function OpenConnectionAccess(ByVal _fileAccess As String) As OleDb.OleDbConnection
            If Microsoft.VisualBasic.Right(_fileAccess, Len(_fileAccess) - _fileAccess.LastIndexOf("."c) - 1) = "mdb" Then
                conAcc = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _fileAccess & ";Persist Security Info=False;")
            ElseIf Microsoft.VisualBasic.Right(_fileAccess, Len(_fileAccess) - _fileAccess.LastIndexOf("."c) - 1) = "accdb" Then
                conAcc = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0 ;Data Source=" & _fileAccess & ";Persist Security Info=False;")
            End If
            conAcc.Open()
            Return conAcc
        End Function

        Public Function createDataTableFromDevExpress(ByVal danhsach As DevExpress.XtraGrid.Views.Grid.GridView, Optional ByVal distinctColumn As String = "") As System.Data.DataTable
            Dim col As DevExpress.XtraGrid.Columns.GridColumn
            Dim tb As New System.Data.DataTable
            For Each col In danhsach.Columns
                Dim coltemp As New System.Data.DataColumn
                If col.FieldName = "wonumber" Or col.FieldName = "item" Then
                    coltemp.ColumnName = col.FieldName
                    tb.Columns.Add(coltemp.ColumnName, Type.GetType("System.String"))
                ElseIf col.FieldName = "totalDay" Or col.FieldName = "oqty" Then
                    coltemp.ColumnName = col.FieldName
                    tb.Columns.Add(coltemp.ColumnName, Type.GetType("System.Double"))
                ElseIf col.FieldName = "maxDate" Or col.FieldName = "minDate" Or col.FieldName = "date" Then
                    coltemp.ColumnName = col.FieldName
                    tb.Columns.Add(coltemp.ColumnName, Type.GetType("System.DateTime"))
                Else
                    coltemp.ColumnName = col.FieldName
                    tb.Columns.Add(coltemp)
                End If
            Next
            Dim dem As Integer = 0
            For i As Integer = 0 To danhsach.DataRowCount - 1
                Dim row As DataRow
                row = tb.NewRow
                If distinctColumn <> "" Then
                    For j As Integer = 0 To tb.Rows.Count - 1
                        dem = 0
                        If danhsach.GetRowCellValue(i, distinctColumn) = tb.Rows(j).Item(distinctColumn).ToString Then
                            dem += 1
                            Exit For
                        End If
                    Next
                End If

                If dem = 0 Then
                    For Each col In danhsach.Columns
                        row(col.FieldName) = danhsach.GetRowCellValue(i, col.FieldName)
                    Next
                    tb.Rows.Add(row)
                End If
            Next
            Return tb
        End Function

        Public Function fillDataset(ByVal con As SqlConnection, ByVal sp_name As String, Optional ByVal param As String = Nothing, Optional ByVal params() As String = Nothing, Optional ByVal values() As Object = Nothing) As DataSet
            Dim ds As New DataSet
            Dim da As New SqlDataAdapter
            Try
                With da
                    Dim cmd As SqlCommand = New SqlCommand(sp_name, con)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandTimeout = 10000
                    Try
                        If (Not params Is Nothing) Then
                            Dim len As Integer = params.Length - 1
                            For i As Integer = 0 To len
                                cmd.Parameters.Add(New SqlParameter(params(i), values(i)))
                            Next
                        End If
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        MessageBox.Show("Error Connection, please contact IT")
                    Finally
                    End Try
                    .SelectCommand = cmd
                    ds.BeginInit()
                    da.Fill(ds, param)
                    ds.EndInit()
                    con.Close()
                End With
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            Return ds
        End Function

        Public Function fillDataset(ByVal ds As DataSet, ByVal con As SqlConnection, ByVal sp_name As String, Optional ByVal param As String = Nothing, Optional ByVal params() As String = Nothing, Optional ByVal values() As Object = Nothing) As DataSet
            Dim da As New SqlDataAdapter
            Try
                With da
                    Dim cmd As SqlCommand = New SqlCommand(sp_name, con)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.CommandTimeout = 10000
                    Try
                        If (Not params Is Nothing) Then
                            Dim len As Integer = params.Length - 1
                            For i As Integer = 0 To len
                                cmd.Parameters.Add(New SqlParameter(params(i), values(i)))
                            Next
                        End If
                        cmd.ExecuteNonQuery()
                    Catch ex As Exception
                        MessageBox.Show("Error Connection, please contact IT")
                    Finally
                    End Try
                    .SelectCommand = cmd
                    ds.BeginInit()
                    da.Fill(ds, param)
                    ds.EndInit()
                    con.Close()
                End With
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            Return ds
        End Function

        Public Sub visibleObject(ByVal parentObj As Object, ByVal flag As Boolean)
            Dim obj As Object
            For Each obj In parentObj.controls
                obj.Enabled = flag
            Next
        End Sub

        'Public Function createDataTableDetail(ByVal tbDetail As System.Data.DataTable) As System.Data.DataTable
        '    If tbDetail.Columns.Count <= 0 Then
        '        Dim colStr As String() = New String() {"colDR_NO", "colClose", "noi_dung", _
        '        "hang_x", "hang_n", "ten_hang", "don_vi", "qui_cach", "kho_x", "nhom_x", "kho_n", "nhom_n", _
        '        "so_luong", "don_gia", "so_tien", "thue_suat", "don_gia_ngte", "so_tien_ngte", "tkno", "c1no", "c2no", _
        '        "tkco", "c1co", "c2co", "link_xem_chitiet"}
        '        For i As Integer = 0 To colStr.Length - 1
        '            tbDetail.Columns.Add(colStr(i))
        '        Next
        '    End If
        '    Return tbDetail
        'End Function

        Public Sub styleTableResult(ByVal tableResult As System.Windows.Forms.DataGridView, ByVal tb As System.Data.DataTable, ByVal flag As Boolean, Optional ByVal optIndex As Integer = 0)
            'Notes-------------         
            'tb      : Datasource cho lưới dữ liệu


            '------------------
            'If tb.Columns.Count <= 0 Then
            '    createDataTableDetail(tb)
            'End If
            tableResult.Columns.Clear()
            Dim col As DataGridViewTextBoxColumn
            Dim colLink As DataGridViewLinkColumn

            'column DR_NO
            colLink = New DataGridViewLinkColumn
            colLink.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            colLink.Name = "colDR_NO"
            colLink.DataPropertyName = "DR_NO"
            colLink.HeaderText = "DR NO."
            colLink.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            colLink.HeaderCell.Style.BackColor = Color.Gainsboro
            colLink.HeaderCell.Style.ForeColor = Color.Brown
            colLink.LinkColor = Color.Red
            colLink.ActiveLinkColor = Color.White
            colLink.VisitedLinkColor = Color.Red

            colLink.LinkBehavior = LinkBehavior.NeverUnderline

            colLink.Width = 90
            colLink.ReadOnly = flag
            colLink.SortMode = DataGridViewColumnSortMode.NotSortable
            'col.Visible = False
            tableResult.Columns.Add(colLink)

            '----------------------------
            'column Status
            Dim colc As DataGridViewCheckBoxColumn
            colc = New DataGridViewCheckBoxColumn
            colc.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            colc.Name = "colClose"
            colc.DataPropertyName = "Close"
            colc.HeaderText = "Status"
            colc.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            colc.HeaderCell.Style.BackColor = Color.Gainsboro
            colc.HeaderCell.Style.ForeColor = Color.Brown
            colc.MinimumWidth = 5
            colc.Width = 50
            colc.ReadOnly = flag
            colc.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(colc)

            '----------------------------
            'column Date Submit
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colDate_sub"
            col.DataPropertyName = "Date_sub"
            col.HeaderText = "Date Submit"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            col.MinimumWidth = 5
            col.Width = 80
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '----------------------------
            'column Description
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "ColDes_of_discr"
            col.DataPropertyName = "Des_of_discr"
            col.HeaderText = "Descriptions"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            col.MinimumWidth = 5
            col.Width = 300
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '------------------------
            'Col Def.Part
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colDef_part_no"
            col.DataPropertyName = "Def_part_no"
            col.HeaderText = "Def.part"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            col.MinimumWidth = 5
            col.Width = 120
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '-------------------------
            'Col Sub.Part
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colSup_part_no"
            col.DataPropertyName = "Sup_part_no"
            col.HeaderText = "Sup.part"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            col.MinimumWidth = 5
            col.Width = 120
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '----------------------------
            'column Source
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colApp_sour_cau_discr"
            col.DataPropertyName = "App_sour_cau_discr"
            col.HeaderText = "Source"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            col.MinimumWidth = 5
            col.Width = 210
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '----------------------------
            'column Id_ctu
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colWO"
            col.DataPropertyName = "WO"
            'col.HeaderText = "WO"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            col.Visible = False
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '----------------------------
            'column nhom_n
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colCon_Act"
            col.DataPropertyName = "Con_Act"
            'col.HeaderText = "Con_Act"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 90
            col.ReadOnly = flag
            col.Visible = False
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '----------------------------
            'column hang_x
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colAct_cau_dis"
            col.DataPropertyName = "Act_cau_dis"
            'col.HeaderText = "Act_cau_dis"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 90
            col.Visible = False
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '----------------------------
            'column hang_n
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colFinal_res"
            col.DataPropertyName = "Final_res"
            'col.HeaderText = "Final_res"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 90
            col.Visible = False
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '-------------------------------
            'column kho_x
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colQA"
            col.DataPropertyName = "QA"
            'col.HeaderText = "QA"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 90
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '-------------------------------
            'column kho_n
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colPD"
            col.DataPropertyName = "PD"
            'col.HeaderText = "PD"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 90
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '----------------------------
            'column so_luong
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colEG"
            col.DataPropertyName = "EG"
            'col.HeaderText = "EG"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 70
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '----------------------------
            'column thue_suat
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colPL"
            col.DataPropertyName = "PL"
            'col.HeaderText = "PL"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 70
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '----------------------------
            'column don_gia
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colWH"
            col.DataPropertyName = "WH"
            'col.HeaderText = "WH"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 100
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)


            '----------------------------
            'column don_gia_ngte
            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            col.Name = "colStatusQA"
            col.DataPropertyName = "Status QA"
            'col.HeaderText = "[Status QA]"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 120
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '----------------------------


            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
            col.Name = "colStatusPD"
            col.DataPropertyName = "Status PD"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 120
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '---------------------------


            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colStatusEG"
            col.DataPropertyName = "Status EG"
            'col.HeaderText = "[Status EG]"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 90
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '--------------------------------

            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colStatusPL"
            col.DataPropertyName = "Status PL"
            'col.HeaderText = "[Status PL]"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 50
            col.ReadOnly = flag
            col.Visible = False
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            tableResult.Columns.Add(col)

            '------------------------

            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.Name = "colStatusWH"
            col.DataPropertyName = "Status WH"
            'col.HeaderText = "[Status WH]"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 70
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)



            '-------------------------

            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colThem_part_no"
            col.DataPropertyName = "Them_part_no"
            col.HeaderText = "Them_part_no"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            col.MinimumWidth = 5
            col.Width = 50
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '-------------------------

            col = New DataGridViewTextBoxColumn
            col.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleLeft
            col.Name = "colSup_name"
            col.DataPropertyName = "Sup_name"
            'col.HeaderText = "Sub_name"
            col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter
            col.HeaderCell.Style.BackColor = Color.Gainsboro
            col.HeaderCell.Style.ForeColor = Color.Brown
            'col.MinimumWidth = 5
            'col.Width = 70
            col.ReadOnly = flag
            col.SortMode = DataGridViewColumnSortMode.NotSortable
            col.Visible = False
            tableResult.Columns.Add(col)

            '----------------------------

            ''Định dạng các thuộc tính màu cho datagrid
            tableResult.ForeColor = Color.Black
            tableResult.AlternatingRowsDefaultCellStyle.BackColor = Color.White
            tableResult.BackColor = Color.Azure
            tableResult.RowsDefaultCellStyle.BackColor = Color.Azure
            'tableResult.
            tableResult.DataSource = tb
        End Sub

        'phuc viet
        'Public Sub DRReportInsert(ByVal In_Pro As Byte, ByVal Re_mat As Byte, ByVal Def_equi As Byte, ByVal Close As Byte, ByVal Fin_part_no As String, ByVal Def_part_no As String, ByVal Wor_center As String, ByVal Total_quan As Integer, ByVal WO As String, ByVal Qty_aff As Integer, ByVal Sup_part_no As String, ByVal Them_part_no As String, ByVal Sup_name As String, ByVal Qty_rec As Integer, ByVal PO As String, ByVal Qty_inspec As Integer, ByVal Equi_no As String, ByVal Descri As String, ByVal Allo_machi As String, ByVal Oper As String, ByVal WO_affec As String, ByVal Des_of_discr As String, ByVal App_sour_cau_discr As String, ByVal Mat_too_no As String, ByVal Repla_tool_no As String, ByVal Dat_time_brok As Date, ByVal Reje_100 As Byte, ByVal Sort_for_use As Byte, ByVal Reject_repla As Byte, ByVal Reject_repla_qty As Integer, ByVal Reject_repla_tracea_ID As String, ByVal Sto_lot As String, ByVal No_contain As String, ByVal Contain_type As String, ByVal Date_sub As Date, ByVal Per_in_cha As String, ByVal Sub_by As String, ByVal Dept As String, ByVal Shift As String, ByVal Con_Act As String, ByVal Scrap_Qty As Integer, ByVal Scrap_Trace As String, ByVal Author As String, ByVal dteDate As Date, ByVal Pro As Byte, ByVal Pur As Byte, ByVal Eng As Byte, ByVal Sales As Byte, ByVal Other As String, ByVal Act_cau_dis As String, ByVal Final_res As String, ByVal CauseOP1 As String, ByVal CauseOP2 As String, ByVal CauseLO As String, ByVal Who As String, ByVal Whom As String, ByVal Dat As Date, ByVal Datee As Date, ByVal Sort_use_qty_acc As Integer, ByVal Sort_use_qty_rej As Integer)
        Public Sub DRReportInsert(ByVal DR_NO As Integer, ByVal Userlogin As String, ByVal In_Pro As Integer, ByVal Re_mat As Byte, ByVal Def_equi As Byte, ByVal Close As Byte, ByVal Fin_part_no As String, ByVal Def_part_no As String, ByVal Wor_center As String, ByVal Total_quan As Integer, ByVal WO As String, ByVal Qty_aff As Integer, ByVal Sup_part_no As String, ByVal Them_part_no As String, ByVal Sup_name As String, ByVal Qty_rec As Integer, ByVal PO As String, ByVal Qty_inspec As Integer, ByVal Equi_no As String, ByVal Descri As String, ByVal Allo_machi As String, ByVal Oper As String, ByVal WO_affec As String, ByVal Des_of_discr As String, ByVal App_sour_cau_discr As String, ByVal Mat_too_no As String, ByVal Repla_tool_no As String, Optional ByVal Dat_time_brok As Object = Nothing, Optional ByVal Reje_100 As Byte = Nothing, Optional ByVal Sort_for_use As Byte = Nothing, Optional ByVal Reject_repla As Byte = Nothing, Optional ByVal Reject_repla_qty As Integer = Nothing, Optional ByVal Reject_repla_tracea_ID As String = Nothing, Optional ByVal Sto_lot As String = Nothing, Optional ByVal No_contain As String = Nothing, Optional ByVal Contain_type As String = Nothing, Optional ByVal Date_sub As Object = Nothing, Optional ByVal Per_in_cha As String = Nothing, Optional ByVal Sub_by As String = Nothing, Optional ByVal Dept As String = Nothing, Optional ByVal Shift As String = Nothing, Optional ByVal Con_Act As String = Nothing, Optional ByVal Scrap_Qty As Integer = Nothing, Optional ByVal Scrap_Trace As String = Nothing, Optional ByVal Author As String = Nothing, Optional ByVal dteDate As Object = Nothing, Optional ByVal Pro As Byte = Nothing, Optional ByVal Pur As Byte = Nothing, Optional ByVal Eng As Byte = Nothing, Optional ByVal Sales As Byte = Nothing, Optional ByVal Other As String = Nothing, Optional ByVal Act_cau_dis As String = Nothing, Optional ByVal Final_res As String = Nothing, Optional ByVal CauseOP1 As String = Nothing, Optional ByVal CauseOP2 As String = Nothing, Optional ByVal CauseLO As String = Nothing, Optional ByVal Who As String = Nothing, Optional ByVal Whom As String = Nothing, Optional ByVal Dat As Object = Nothing, Optional ByVal Datee As Object = Nothing, Optional ByVal Tri_1 As String = Nothing, Optional ByVal Tri_2 As String = Nothing, Optional ByVal Tri_3 As String = Nothing, Optional ByVal Tri_4 As String = Nothing, Optional ByVal Tri_5 As String = Nothing, Optional ByVal Tri_6 As String = Nothing, Optional ByVal Tri_7 As String = Nothing, Optional ByVal Tri_8 As Double = Nothing, Optional ByVal Tri_9 As Double = Nothing, Optional ByVal Tri_10 As Double = Nothing, Optional ByVal Tri_11 As Double = Nothing, Optional ByVal Tri_12 As String = Nothing, Optional ByVal Tri_13 As String = Nothing, Optional ByVal Tri_14 As String = Nothing, Optional ByVal Tri_15 As String = Nothing, Optional ByVal Kind_of_mat As String = Nothing, Optional ByVal Issue_code As String = Nothing, Optional ByVal Issue_sourse As String = Nothing, Optional ByVal Key_point As String = Nothing, Optional ByVal Subm_to As String = Nothing, Optional ByVal Detec_sta As Byte = Nothing, Optional ByVal QA As Byte = Nothing, Optional ByVal PD As Byte = Nothing, Optional ByVal EG As Byte = Nothing, Optional ByVal PL As Byte = Nothing, Optional ByVal WH As Byte = Nothing, Optional ByVal StatusQA As Byte = Nothing, Optional ByVal StatusPD As Byte = Nothing, Optional ByVal StatusEG As Byte = Nothing, Optional ByVal StatusPL As Byte = Nothing, Optional ByVal StatusWH As Byte = Nothing, Optional ByVal Data_Entry As Byte = Nothing, Optional ByVal Instructions As String = Nothing, Optional ByVal Sort_use_qty_acc As Integer = Nothing, Optional ByVal Sort_use_qty_rej As Integer = Nothing)
            Dim cn As New ERP.CONNECTION
            Dim cmd As New SqlCommand("tvphucDRReportInsert", cn.OpenConnectionSQL("factory"))
            cmd.CommandType = CommandType.StoredProcedure
            'them------
            cmd.Parameters.Add(New SqlParameter("@DR_NO", SqlDbType.Int)).Value = DR_NO
            '----------
            cmd.Parameters.Add(New SqlParameter("@In_pro", SqlDbType.Bit)).Value = In_Pro
            cmd.Parameters.Add(New SqlParameter("@Re_mat", SqlDbType.Bit)).Value = Re_mat
            cmd.Parameters.Add(New SqlParameter("@Def_equi", SqlDbType.Bit)).Value = Def_equi
            cmd.Parameters.Add(New SqlParameter("@Close", SqlDbType.Bit)).Value = Close
            cmd.Parameters.Add(New SqlParameter("@Fin_part_no", SqlDbType.NVarChar)).Value = Fin_part_no
            cmd.Parameters.Add(New SqlParameter("@Def_part_no", SqlDbType.NVarChar)).Value = Def_part_no
            cmd.Parameters.Add(New SqlParameter("@Wor_center", SqlDbType.NVarChar)).Value = Wor_center
            cmd.Parameters.Add(New SqlParameter("@Total_quan", SqlDbType.Int)).Value = Total_quan
            cmd.Parameters.Add(New SqlParameter("@WO", SqlDbType.NVarChar)).Value = WO
            cmd.Parameters.Add(New SqlParameter("@Qty_aff", SqlDbType.Int)).Value = Qty_aff
            cmd.Parameters.Add(New SqlParameter("@Sup_part_no", SqlDbType.NVarChar)).Value = Sup_part_no
            cmd.Parameters.Add(New SqlParameter("@Them_part_no", SqlDbType.NVarChar)).Value = Them_part_no
            cmd.Parameters.Add(New SqlParameter("@Sup_name", SqlDbType.NVarChar)).Value = Sup_name
            cmd.Parameters.Add(New SqlParameter("@Qty_rec", SqlDbType.Int)).Value = Qty_rec
            cmd.Parameters.Add(New SqlParameter("@PO", SqlDbType.NVarChar)).Value = PO
            cmd.Parameters.Add(New SqlParameter("@Qty_inspec", SqlDbType.Int)).Value = Qty_inspec
            cmd.Parameters.Add(New SqlParameter("@Equi_no", SqlDbType.NVarChar)).Value = Equi_no
            cmd.Parameters.Add(New SqlParameter("@Descri", SqlDbType.NVarChar)).Value = Descri
            cmd.Parameters.Add(New SqlParameter("@Allo_machi", SqlDbType.NVarChar)).Value = Allo_machi
            cmd.Parameters.Add(New SqlParameter("@Oper", SqlDbType.NVarChar)).Value = Oper
            cmd.Parameters.Add(New SqlParameter("@WO_affec", SqlDbType.NVarChar)).Value = WO_affec
            cmd.Parameters.Add(New SqlParameter("@Des_of_discr", SqlDbType.NVarChar)).Value = Des_of_discr
            cmd.Parameters.Add(New SqlParameter("@App_sour_cau_discr", SqlDbType.NVarChar)).Value = App_sour_cau_discr
            cmd.Parameters.Add(New SqlParameter("@Mat_too_no", SqlDbType.NVarChar)).Value = Mat_too_no
            cmd.Parameters.Add(New SqlParameter("@Repla_tool_no", SqlDbType.NVarChar)).Value = Repla_tool_no
            cmd.Parameters.Add(New SqlParameter("@Dat_time_brok", Dat_time_brok))
            cmd.Parameters.Add(New SqlParameter("@Reje_100", SqlDbType.Bit)).Value = Reje_100
            cmd.Parameters.Add(New SqlParameter("@Sort_for_use", SqlDbType.Bit)).Value = Sort_for_use
            cmd.Parameters.Add(New SqlParameter("@Reject_repla", SqlDbType.Bit)).Value = Reject_repla
            cmd.Parameters.Add(New SqlParameter("@Reject_repla_qty", SqlDbType.Int)).Value = Reject_repla_qty
            cmd.Parameters.Add(New SqlParameter("@Reject_repla_tracea_ID", SqlDbType.NVarChar)).Value = Reject_repla_tracea_ID
            cmd.Parameters.Add(New SqlParameter("@Sto_lot", SqlDbType.NVarChar)).Value = Sto_lot
            cmd.Parameters.Add(New SqlParameter("@No_contain", SqlDbType.NVarChar)).Value = No_contain
            cmd.Parameters.Add(New SqlParameter("@Contain_type", SqlDbType.NVarChar)).Value = Contain_type
            cmd.Parameters.Add(New SqlParameter("@Date_sub", Date_sub))
            cmd.Parameters.Add(New SqlParameter("@Per_in_cha", SqlDbType.NVarChar)).Value = Per_in_cha
            cmd.Parameters.Add(New SqlParameter("@Sub_by", SqlDbType.NVarChar)).Value = Sub_by
            cmd.Parameters.Add(New SqlParameter("@Dept", SqlDbType.NVarChar)).Value = Dept
            cmd.Parameters.Add(New SqlParameter("@Shift", SqlDbType.NVarChar)).Value = Shift
            cmd.Parameters.Add(New SqlParameter("@Con_Act", SqlDbType.NVarChar)).Value = Con_Act
            cmd.Parameters.Add(New SqlParameter("@Scrap_Qty", SqlDbType.NVarChar)).Value = Scrap_Qty
            cmd.Parameters.Add(New SqlParameter("@Scrap_Trace", SqlDbType.NVarChar)).Value = Scrap_Trace
            cmd.Parameters.Add(New SqlParameter("@Author", SqlDbType.NVarChar)).Value = Author
            cmd.Parameters.Add(New SqlParameter("@Date", dteDate))
            cmd.Parameters.Add(New SqlParameter("@Pro", SqlDbType.Bit)).Value = Pro
            cmd.Parameters.Add(New SqlParameter("@Pur", SqlDbType.Bit)).Value = Pur
            cmd.Parameters.Add(New SqlParameter("@Eng", SqlDbType.Bit)).Value = Eng
            cmd.Parameters.Add(New SqlParameter("@Sales", SqlDbType.Bit)).Value = Sales
            cmd.Parameters.Add(New SqlParameter("@Other", SqlDbType.NVarChar)).Value = Other
            cmd.Parameters.Add(New SqlParameter("@Act_cau_dis", SqlDbType.NVarChar)).Value = Act_cau_dis
            cmd.Parameters.Add(New SqlParameter("@Final_res", SqlDbType.NVarChar)).Value = Final_res
            cmd.Parameters.Add(New SqlParameter("@CauseOP1", SqlDbType.NVarChar)).Value = CauseOP1
            cmd.Parameters.Add(New SqlParameter("@CauseOP2", SqlDbType.NVarChar)).Value = CauseOP2
            cmd.Parameters.Add(New SqlParameter("@CauseLO", SqlDbType.NVarChar)).Value = CauseLO
            cmd.Parameters.Add(New SqlParameter("@Who", SqlDbType.NVarChar)).Value = Who
            cmd.Parameters.Add(New SqlParameter("@Whom", SqlDbType.NVarChar)).Value = Whom
            cmd.Parameters.Add(New SqlParameter("@Dat", Dat))
            cmd.Parameters.Add(New SqlParameter("@Datee", Datee))
            cmd.Parameters.Add(New SqlParameter("@Sort_use_qty_acc", SqlDbType.Int)).Value = Sort_use_qty_acc
            cmd.Parameters.Add(New SqlParameter("@Sort_use_qty_rej", SqlDbType.Int)).Value = Sort_use_qty_rej
            cmd.Parameters.Add(New SqlParameter("@UserLogin", SqlDbType.VarChar)).Value = Userlogin

            cmd.ExecuteNonQuery()
            cn.OpenConnectionSQL("factory").Close()
        End Sub

        Public Sub DRReportUpdate(ByVal DR_NO As Integer, ByVal In_Pro As Integer, ByVal Re_mat As Byte, ByVal Def_equi As Byte, ByVal Close As Byte, ByVal Fin_part_no As String, ByVal Def_part_no As String, ByVal Wor_center As String, ByVal Total_quan As Integer, ByVal WO As String, ByVal Qty_aff As Integer, ByVal Sup_part_no As String, ByVal Them_part_no As String, ByVal Sup_name As String, ByVal Qty_rec As Integer, ByVal PO As String, ByVal Qty_inspec As Integer, ByVal Equi_no As String, ByVal Descri As String, ByVal Allo_machi As String, ByVal Oper As String, ByVal WO_affec As String, ByVal Des_of_discr As String, ByVal App_sour_cau_discr As String, ByVal Mat_too_no As String, ByVal Repla_tool_no As String, Optional ByVal Dat_time_brok As Object = Nothing, Optional ByVal Reje_100 As Byte = Nothing, Optional ByVal Sort_for_use As Byte = Nothing, Optional ByVal Reject_repla As Byte = Nothing, Optional ByVal Reject_repla_qty As Integer = Nothing, Optional ByVal Reject_repla_tracea_ID As String = Nothing, Optional ByVal Sto_lot As String = Nothing, Optional ByVal No_contain As String = Nothing, Optional ByVal Contain_type As String = Nothing, Optional ByVal Date_sub As Object = Nothing, Optional ByVal Per_in_cha As String = Nothing, Optional ByVal Sub_by As String = Nothing, Optional ByVal Dept As String = Nothing, Optional ByVal Shift As String = Nothing, Optional ByVal Con_Act As String = Nothing, Optional ByVal Scrap_Qty As Integer = Nothing, Optional ByVal Scrap_Trace As String = Nothing, Optional ByVal Author As String = Nothing, Optional ByVal dteDate As Object = Nothing, Optional ByVal Pro As Byte = Nothing, Optional ByVal Pur As Byte = Nothing, Optional ByVal Eng As Byte = Nothing, Optional ByVal Sales As Byte = Nothing, Optional ByVal Other As String = Nothing, Optional ByVal Act_cau_dis As String = Nothing, Optional ByVal Final_res As String = Nothing, Optional ByVal CauseOP1 As String = Nothing, Optional ByVal CauseOP2 As String = Nothing, Optional ByVal CauseLO As String = Nothing, Optional ByVal Who As String = Nothing, Optional ByVal Whom As String = Nothing, Optional ByVal Dat As Object = Nothing, Optional ByVal Datee As Object = Nothing, Optional ByVal Tri_1 As String = Nothing, Optional ByVal Tri_2 As String = Nothing, Optional ByVal Tri_3 As String = Nothing, Optional ByVal Tri_4 As String = Nothing, Optional ByVal Tri_5 As String = Nothing, Optional ByVal Tri_6 As String = Nothing, Optional ByVal Tri_7 As String = Nothing, Optional ByVal Tri_8 As Double = Nothing, Optional ByVal Tri_9 As Double = Nothing, Optional ByVal Tri_10 As Double = Nothing, Optional ByVal Tri_11 As Double = Nothing, Optional ByVal Tri_12 As String = Nothing, Optional ByVal Tri_13 As String = Nothing, Optional ByVal Tri_14 As String = Nothing, Optional ByVal Tri_15 As String = Nothing, Optional ByVal Kind_of_mat As String = Nothing, Optional ByVal Issue_code As String = Nothing, Optional ByVal Issue_sourse As String = Nothing, Optional ByVal Key_point As String = Nothing, Optional ByVal Subm_to As String = Nothing, Optional ByVal Detec_sta As Byte = Nothing, Optional ByVal QA As Byte = Nothing, Optional ByVal PD As Byte = Nothing, Optional ByVal EG As Byte = Nothing, Optional ByVal PL As Byte = Nothing, Optional ByVal WH As Byte = Nothing, Optional ByVal StatusQA As Byte = Nothing, Optional ByVal StatusPD As Byte = Nothing, Optional ByVal StatusEG As Byte = Nothing, Optional ByVal StatusPL As Byte = Nothing, Optional ByVal StatusWH As Byte = Nothing, Optional ByVal Data_Entry As Byte = Nothing, Optional ByVal Instructions As String = Nothing, Optional ByVal Sort_use_qty_acc As Integer = Nothing, Optional ByVal Sort_use_qty_rej As Integer = Nothing)
            Dim cn As New ERP.CONNECTION
            Dim cmd As New SqlCommand("tvphucDRReportUpdate", cn.OpenConnectionSQL("factory"))
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New SqlParameter("@DR_NO", SqlDbType.Float)).Value = DR_NO
            cmd.Parameters.Add(New SqlParameter("@In_pro", SqlDbType.Bit)).Value = In_Pro
            cmd.Parameters.Add(New SqlParameter("@Re_mat", SqlDbType.Bit)).Value = Re_mat
            cmd.Parameters.Add(New SqlParameter("@Def_equi", SqlDbType.Bit)).Value = Def_equi
            cmd.Parameters.Add(New SqlParameter("@Close", SqlDbType.Bit)).Value = Close
            cmd.Parameters.Add(New SqlParameter("@Fin_part_no", SqlDbType.NVarChar)).Value = Fin_part_no
            cmd.Parameters.Add(New SqlParameter("@Def_part_no", SqlDbType.NVarChar)).Value = Def_part_no
            cmd.Parameters.Add(New SqlParameter("@Wor_center", SqlDbType.NVarChar)).Value = Wor_center
            cmd.Parameters.Add(New SqlParameter("@Total_quan", SqlDbType.Int)).Value = Total_quan
            cmd.Parameters.Add(New SqlParameter("@WO", SqlDbType.NVarChar)).Value = WO
            cmd.Parameters.Add(New SqlParameter("@Qty_aff", SqlDbType.Int)).Value = Qty_aff
            cmd.Parameters.Add(New SqlParameter("@Sup_part_no", SqlDbType.NVarChar)).Value = Sup_part_no
            cmd.Parameters.Add(New SqlParameter("@Them_part_no", SqlDbType.NVarChar)).Value = Them_part_no
            cmd.Parameters.Add(New SqlParameter("@Sup_name", SqlDbType.NVarChar)).Value = Sup_name
            cmd.Parameters.Add(New SqlParameter("@Qty_rec", SqlDbType.Int)).Value = Qty_rec
            cmd.Parameters.Add(New SqlParameter("@PO", SqlDbType.NVarChar)).Value = PO
            cmd.Parameters.Add(New SqlParameter("@Qty_inspec", SqlDbType.Int)).Value = Qty_inspec
            cmd.Parameters.Add(New SqlParameter("@Equi_no", SqlDbType.NVarChar)).Value = Equi_no
            cmd.Parameters.Add(New SqlParameter("@Descri", SqlDbType.NVarChar)).Value = Descri
            cmd.Parameters.Add(New SqlParameter("@Allo_machi", SqlDbType.NVarChar)).Value = Allo_machi
            cmd.Parameters.Add(New SqlParameter("@Oper", SqlDbType.NVarChar)).Value = Oper
            cmd.Parameters.Add(New SqlParameter("@WO_affec", SqlDbType.NVarChar)).Value = WO_affec
            cmd.Parameters.Add(New SqlParameter("@Des_of_discr", SqlDbType.NVarChar)).Value = Des_of_discr
            cmd.Parameters.Add(New SqlParameter("@App_sour_cau_discr", SqlDbType.NVarChar)).Value = App_sour_cau_discr
            cmd.Parameters.Add(New SqlParameter("@Mat_too_no", SqlDbType.NVarChar)).Value = Mat_too_no
            cmd.Parameters.Add(New SqlParameter("@Repla_tool_no", SqlDbType.NVarChar)).Value = Repla_tool_no
            cmd.Parameters.Add(New SqlParameter("@Dat_time_brok", Dat_time_brok))
            cmd.Parameters.Add(New SqlParameter("@Reje_100", SqlDbType.Bit)).Value = Reje_100
            cmd.Parameters.Add(New SqlParameter("@Sort_for_use", SqlDbType.TinyInt)).Value = Sort_for_use
            cmd.Parameters.Add(New SqlParameter("@Reject_repla", SqlDbType.TinyInt)).Value = Reject_repla
            cmd.Parameters.Add(New SqlParameter("@Reject_repla_qty", SqlDbType.Int)).Value = Reject_repla_qty
            cmd.Parameters.Add(New SqlParameter("@Reject_repla_tracea_ID", SqlDbType.NVarChar)).Value = Reject_repla_tracea_ID
            cmd.Parameters.Add(New SqlParameter("@Sto_lot", SqlDbType.NVarChar)).Value = Sto_lot
            cmd.Parameters.Add(New SqlParameter("@No_contain", SqlDbType.NVarChar)).Value = No_contain
            cmd.Parameters.Add(New SqlParameter("@Contain_type", SqlDbType.NVarChar)).Value = Contain_type
            cmd.Parameters.Add(New SqlParameter("@Date_sub", Date_sub))
            cmd.Parameters.Add(New SqlParameter("@Per_in_cha", SqlDbType.NVarChar)).Value = Per_in_cha
            cmd.Parameters.Add(New SqlParameter("@Sub_by", SqlDbType.NVarChar)).Value = Sub_by
            cmd.Parameters.Add(New SqlParameter("@Dept", SqlDbType.NVarChar)).Value = Dept
            cmd.Parameters.Add(New SqlParameter("@Shift", SqlDbType.NVarChar)).Value = Shift
            cmd.Parameters.Add(New SqlParameter("@Con_Act", SqlDbType.NVarChar)).Value = Con_Act
            cmd.Parameters.Add(New SqlParameter("@Scrap_Qty", SqlDbType.NVarChar)).Value = Scrap_Qty
            cmd.Parameters.Add(New SqlParameter("@Scrap_Trace", SqlDbType.NVarChar)).Value = Scrap_Trace
            cmd.Parameters.Add(New SqlParameter("@Author", SqlDbType.NVarChar)).Value = Author
            cmd.Parameters.Add(New SqlParameter("@Date", dteDate))
            cmd.Parameters.Add(New SqlParameter("@Pro", SqlDbType.TinyInt)).Value = Pro
            cmd.Parameters.Add(New SqlParameter("@Pur", SqlDbType.TinyInt)).Value = Pur
            cmd.Parameters.Add(New SqlParameter("@Eng", SqlDbType.TinyInt)).Value = Eng
            cmd.Parameters.Add(New SqlParameter("@Sales", SqlDbType.TinyInt)).Value = Sales
            cmd.Parameters.Add(New SqlParameter("@Other", SqlDbType.NVarChar)).Value = Other
            cmd.Parameters.Add(New SqlParameter("@Act_cau_dis", SqlDbType.NVarChar)).Value = Act_cau_dis
            cmd.Parameters.Add(New SqlParameter("@Final_res", SqlDbType.NVarChar)).Value = Final_res
            cmd.Parameters.Add(New SqlParameter("@CauseOP1", SqlDbType.NVarChar)).Value = CauseOP1
            cmd.Parameters.Add(New SqlParameter("@CauseOP2", SqlDbType.NVarChar)).Value = CauseOP2
            cmd.Parameters.Add(New SqlParameter("@CauseLO", SqlDbType.NVarChar)).Value = CauseLO
            cmd.Parameters.Add(New SqlParameter("@Who", SqlDbType.NVarChar)).Value = Who
            cmd.Parameters.Add(New SqlParameter("@Whom", SqlDbType.NVarChar)).Value = Whom
            cmd.Parameters.Add(New SqlParameter("@Dat", Dat))
            cmd.Parameters.Add(New SqlParameter("@Datee", Datee))
            cmd.Parameters.Add(New SqlParameter("@Tri_1", SqlDbType.NVarChar)).Value = Tri_1
            cmd.Parameters.Add(New SqlParameter("@Tri_2", SqlDbType.NVarChar)).Value = Tri_2
            cmd.Parameters.Add(New SqlParameter("@Tri_3", SqlDbType.NVarChar)).Value = Tri_3
            cmd.Parameters.Add(New SqlParameter("@Tri_4", SqlDbType.NVarChar)).Value = Tri_4
            cmd.Parameters.Add(New SqlParameter("@Tri_5", SqlDbType.NVarChar)).Value = Tri_5
            cmd.Parameters.Add(New SqlParameter("@Tri_6", SqlDbType.NVarChar)).Value = Tri_6
            cmd.Parameters.Add(New SqlParameter("@Tri_7", SqlDbType.NVarChar)).Value = Tri_7
            cmd.Parameters.Add(New SqlParameter("@Tri_8", SqlDbType.Float)).Value = Tri_8
            cmd.Parameters.Add(New SqlParameter("@Tri_9", SqlDbType.Float)).Value = Tri_9
            cmd.Parameters.Add(New SqlParameter("@Tri_10", SqlDbType.Float)).Value = Tri_10
            cmd.Parameters.Add(New SqlParameter("@Tri_11", SqlDbType.Float)).Value = Tri_11
            cmd.Parameters.Add(New SqlParameter("@Tri_12", SqlDbType.NVarChar)).Value = Tri_12
            cmd.Parameters.Add(New SqlParameter("@Tri_13", SqlDbType.NVarChar)).Value = Tri_13
            cmd.Parameters.Add(New SqlParameter("@Tri_14", SqlDbType.NVarChar)).Value = Tri_14
            cmd.Parameters.Add(New SqlParameter("@Tri_15", SqlDbType.NVarChar)).Value = Tri_15
            cmd.Parameters.Add(New SqlParameter("@Kind_of_mat", SqlDbType.NVarChar)).Value = Kind_of_mat
            cmd.Parameters.Add(New SqlParameter("@Issue_code", SqlDbType.NVarChar)).Value = Issue_code
            cmd.Parameters.Add(New SqlParameter("@Issue_sourse", SqlDbType.NVarChar)).Value = Issue_sourse
            cmd.Parameters.Add(New SqlParameter("@Key_point", SqlDbType.NVarChar)).Value = Key_point
            cmd.Parameters.Add(New SqlParameter("@Subm_to", SqlDbType.NVarChar)).Value = Subm_to
            cmd.Parameters.Add(New SqlParameter("@Detec_sta", SqlDbType.Bit)).Value = Detec_sta
            cmd.Parameters.Add(New SqlParameter("@QA", SqlDbType.Bit)).Value = QA
            cmd.Parameters.Add(New SqlParameter("@PD", SqlDbType.Bit)).Value = PD
            cmd.Parameters.Add(New SqlParameter("@EG", SqlDbType.Bit)).Value = EG
            cmd.Parameters.Add(New SqlParameter("@PL", SqlDbType.Bit)).Value = PL
            cmd.Parameters.Add(New SqlParameter("@WH", SqlDbType.Bit)).Value = WH
            cmd.Parameters.Add(New SqlParameter("@StatusQA", SqlDbType.Bit)).Value = StatusQA
            cmd.Parameters.Add(New SqlParameter("@StatusPD", SqlDbType.Bit)).Value = StatusPD
            cmd.Parameters.Add(New SqlParameter("@StatusEG", SqlDbType.Bit)).Value = StatusEG
            cmd.Parameters.Add(New SqlParameter("@StatusPL", SqlDbType.Bit)).Value = StatusPL
            cmd.Parameters.Add(New SqlParameter("@StatusWH", SqlDbType.Bit)).Value = StatusWH
            cmd.Parameters.Add(New SqlParameter("@Data_Entry", SqlDbType.Bit)).Value = Data_Entry
            cmd.Parameters.Add(New SqlParameter("@Instructions", SqlDbType.NVarChar)).Value = Instructions
            cmd.Parameters.Add(New SqlParameter("@Sort_use_qty_acc", SqlDbType.Int)).Value = Sort_use_qty_acc
            cmd.Parameters.Add(New SqlParameter("@Sort_use_qty_rej", SqlDbType.Int)).Value = Sort_use_qty_rej
            cmd.ExecuteNonQuery()
            cn.OpenConnectionSQL("factory").Close()
        End Sub

    End Class


#End Region

#Region " CLASS CONNECTION "

    Public Class CONNECTION
        Private _Server As String
        Private _Database As String
        Private _Userid As String = "sa"
        Private _Password As String = "ttfi_1985"

        Public Property Server() As String
            Get
                Return _Server
            End Get
            Set(ByVal value As String)
                _Server = value
            End Set
        End Property
        Public Property Database() As String
            Get
                Return _Database
            End Get
            Set(ByVal value As String)
                _Database = value
            End Set
        End Property
        Public Property Userid() As String
            Get
                Return _Userid
            End Get
            Set(ByVal value As String)
                _Userid = value
            End Set
        End Property
        Public Property Password() As String
            Get
                Return _Password
            End Get
            Set(ByVal value As String)
                _Password = value
            End Set
        End Property

        Public Function connectExcel(ByVal path As String) As OleDb.OleDbConnection
            Dim connect As String
            If Microsoft.VisualBasic.Right(path, path.Length - path.LastIndexOf(".")) = "xls" Then
                connect = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source= '" + path + "';" + "Extended Properties='Excel 4.0;HDR=Yes;IMEX=1;'"
            Else
                connect = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source= '" + path + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
            End If
            Dim conn As New OleDb.OleDbConnection(connect)
            Return conn

        End Function

        Public Function stringConnect(ByVal db As String) As String

            If db.ToLower = "admin" Then
                'sv = "10.0.0.231\SQLserver2008"
                Return "data source=10.0.0.231\SQLserver2008;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "factory" Then
                Return "data source=VSIP-SERVER02\SQLSERVER;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "woprinting" Then
                Return "data source=10.0.0.231\SQLserver2008;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "cfm" Then
                Return "data source=10.0.0.231\SQLserver2008;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "systemcontrol" Then
                Return "data source=10.0.0.231\SQLserver2008;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "tvn_warehouse" Then
                'sv = "10.0.0.2\SQLserver2008"
                Return "data source=10.0.0.2\SQLserver2008;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "warehouse" Then
                'sv = "10.0.0.2\SQLserver2008"
                Return "data source=10.0.0.2\SQLserver2008;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            ElseIf db.ToLower = "bbmxserver" Then
                Return "data source=VSIP-SERVER02\SQLSERVER;initial catalog=" & db & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
            End If
            Return "Server is not connect, please contact IT !"

        End Function
        
        Public Function OpenConnectionSQL() As SqlConnection
            Dim cn As New SqlConnection
            Try
                Dim conString As String
                conString = "data source=" & _Server & ";initial catalog=" & _Database & ";persist security info=False;user id=" & _Userid & ";pwd=" & _Password
                cn = New SqlConnection(conString)
                cn.Open()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try
            Return cn
        End Function
        Public Function OpenConnectionSQL(ByVal strCon As String) As SqlConnection
            'strCon="admin" : Database admin server 231
            'strCon="factory": Database factory server 02
            'If OpenConnectionSQL(strCon).State = ConnectionState.Open Then
            '    OpenConnectionSQL(strCon).close()
            'End If
            Dim cn As New SqlConnection
            Try
                cn = New SqlConnection(stringConnect(strCon))
                cn.Open()
            Catch ex As System.Exception
                MsgBox(ex.ToString)
            End Try
            Return cn
        End Function

        Public Function closeConnection(ByVal cn As SqlConnection) As SqlConnection
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
            Return cn
        End Function

    End Class
#End Region

#Region " CLASS WOPRINTING "

    Public Class WOPRINTING

#Region " Autocomplete WOPrinting "
        Public Sub AutoComplete(ByVal cbo As ComboBox, _
          ByVal e As System.Windows.Forms.KeyEventArgs)
            ' Call this from your form passing in the name 

            ' of your combobox and the event arg:

            ' AutoComplete(cboState, e)

            Dim iIndex As Integer
            Dim sActual As String
            Dim sFound As String
            Dim bMatchFound As Boolean

            'check if the text is blank or not, if not then only proceed

            If Not cbo.Text = "" Then 'if the text is not blank then only proceed


                ' If backspace then remove the last character 

                ' that was typed in and try to find 

                ' a match. note that the selected text from the 

                ' last character typed in to the 

                ' end of the combo text field will also be deleted.

                If e.KeyCode = Keys.Back Then
                    cbo.Text = Mid(cbo.Text, 1, Len(cbo.Text) - 1)
                End If

                ' Do nothing for some keys such as navigation keys...

                If ((e.KeyCode = Keys.Left) Or _
                 (e.KeyCode = Keys.Right) Or _
                 (e.KeyCode = Keys.Up) Or _
                 (e.KeyCode = Keys.Down) Or _
                 (e.KeyCode = Keys.PageUp) Or _
                 (e.KeyCode = Keys.PageDown) Or _
                 (e.KeyCode = Keys.Home) Or _
                 (e.KeyCode = Keys.End)) Then
                    Return
                End If


                Do
                    ' Store the actual text that has been typed.

                    sActual = cbo.Text
                    ' Find the first match for the typed value.

                    iIndex = cbo.FindString(sActual)
                    ' Get the text of the first match.

                    ' if index > -1 then a match was found.


                    If (iIndex > -1) Then '** FOUND SECTION **

                        sFound = cbo.Items(iIndex).ToString()
                        ' Select this item from the list.

                        cbo.SelectedIndex = iIndex
                        ' Select the portion of the text that was automatically

                        ' added so that additional typing will replace it.

                        cbo.SelectionStart = sActual.Length
                        cbo.SelectionLength = sFound.Length
                        bMatchFound = True
                    Else '** NOT FOUND SECTION **


                        'if there isn't a match and the text typed in is only 1 character 

                        'or nothing then just select the first entry in the combo box.


                        If sActual.Length = 1 Or sActual.Length = 0 Then
                            cbo.SelectedIndex = 0
                            cbo.SelectionStart = 0
                            cbo.SelectionLength = Len(cbo.Text)
                            bMatchFound = True

                        Else

                            'if there isn't a match for the text typed in then 

                            'remove the last character of the text typed in

                            'and try to find a match.


                            '************************** NOTE **************************

                            'COMMENT THE FOLLOWING THREE LINES AND UNCOMMENT 

                            'THE (bMatchFound = True) LINE 

                            'INCASE YOU WANT TO ALLOW TEXTS TO BE TYPED IN

                            ' WHICH ARE NOT IN THE LIST. ELSE IF 

                            'YOU WANT TO RESTRICT THE USER TO TYPE TEXTS WHICH ARE 

                            'NOT IN THE LIST THEN LEAVE THE FOLLOWING THREE LINES AS IT IS

                            'AND COMMENT THE (bMatchFound = True) LINE.

                            '***********************************************************

                            '/////// THREE LINES SECTION STARTS HERE ///////////

                            cbo.SelectionStart = sActual.Length - 1
                            cbo.SelectionLength = sActual.Length - 1
                            cbo.Text = Mid(cbo.Text, 1, Len(cbo.Text) - 1)
                            '/////// THREE LINES SECTION ENDS HERE /////////////

                            'bMatchFound = True


                        End If

                    End If

                Loop Until bMatchFound

            End If

        End Sub

#End Region

    End Class

#End Region

    Public Class USERS
        Public Sub CreateUserLogin(ByVal code As String, ByVal password As String, ByVal levelId As Integer)
            'Dim strConnection As String = HRConnectionClass.GetConnectionString("ConnectionString")
            'Dim cn As New SqlConnection(strConnection)
            Dim cn As New ERP.CONNECTION
            Dim cmd As New SqlCommand("CreateUserLogin", cn.OpenConnectionSQL("factory"))
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add(New SqlParameter("@Code", SqlDbType.VarChar, 5))
            cmd.Parameters.Add(New SqlParameter("@Password", SqlDbType.VarChar, 50))
            cmd.Parameters.Add(New SqlParameter("@LevelID", SqlDbType.TinyInt))
            cmd.Parameters("@Code").Value = code
            cmd.Parameters("@Password").Value = password
            cmd.Parameters("@LevelID").Value = levelId
            Try
                'cn.Open()
                cmd.ExecuteNonQuery()
            Finally
                'cn.Close()
            End Try
        End Sub
    End Class

End Class
